package com.iqiyi.pps.epg.core.dao.base;

import com.iqiyi.kiwi.utils.ReflectionUtils;
import com.iqiyi.pps.epg.core.model.base.BaseEntity;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 用于项目的Hibernate工具类.
 *
 * @author calvin
 */
public class HibernateUtils {
    private static Logger logger = LoggerFactory.getLogger(HibernateUtils.class);
    private static final String SQL_COUNT_PREFIC="count(1)";

    /**
     * 根据对象ID集合, 整理合并集合.
     *
     * @param srcObjects 源集合,元素为对象.
     * @param checkedIds  目标集合,元素为ID.
     * @param clazz  集合中对象的类型,必须为IdEntity子类
     * @param <T>   泛型
     */
    public static <T extends BaseEntity> void mergeByCheckedIds(final Collection<T> srcObjects,
                                                                final Collection<Long> checkedIds, final Class<T> clazz) {

        //参数校验
//		Assert.notNull(srcObjects, "scrObjects不能为空");
//		Assert.notNull(clazz, "clazz不能为空");

        //目标集合为空, 删除源集合中所有对象后直接返回.
        if (checkedIds == null) {
            srcObjects.clear();
//            checkedIds.clear();
            return ;
        }

        //遍历源对象集合,如果其id不在目标ID集合中的对象删除.
        //同时,在目标集合中删除已在源集合中的id,使得目标集合中剩下的id均为源集合中没有的id.
        Iterator<T> srcIterator = srcObjects.iterator();
        try {

            while (srcIterator.hasNext()) {
                T element = srcIterator.next();
                Long id = element.getId();

                if (!checkedIds.contains(id)) {
                    srcIterator.remove();
                } else {
                    checkedIds.remove(id);
                }
            }

            //ID集合目前剩余的id均不在源集合中,创建对象,为id属性赋值并添加到源集合中.
//			for (Long id : checkedIds) {
//				T element = clazz.newInstance();
//				element.setId(id);
//				srcObjects.add(element);
//			}
        } catch (Exception e) {
            throw ReflectionUtils.convertReflectionExceptionToUnchecked(e);
        }
    }

    /**
     * 根据传入的指定类名解析查询所得的结果，并返回转为javaBean的集合
     * @param session
     * @param sql
     * @param parm
     * @param clazzName
     * @return
     */
    public static List executeQueryBeanList(Session session, String sql, Object[] parm, String clazzName){
        List<Map<String,Object>> result=executeQuery(session,sql,parm);
        return convertToBeanList(result,clazzName);
    }

    /**
     * 根据传入的指定类名解析查询所得的结果，并返回转为单个的javaBean
     * @param session
     * @param sql
     * @param parm
     * @param clazzName
     * @return
     */
    public static Object executeQueryBean(Session session,String sql,Object[] parm,String clazzName){
        List<Map<String,Object>> result=executeQuery(session,sql,parm);
        if(result==null||result.size()<=0){
            return null;
        }
        return convertToBean(result.get(0),clazzName);
    }
    /**
     * 执行count语句
     * @param session
     * @param sql   不能包含order by子句，
     * @param parm 参数务必与from后的?保持一致，并且止于order by
     * @return
     */
    public static long executeCount(Session session,String sql,Object[] parm){
        String sql_count=StringUtils.lowerCase(sql); //全部转为小写
        sql_count=getCountSql(sql_count);   //获取sql_count语句
        Long total=null;
        if(sql_count!=null&&sql_count.length()>0){
            List<Map<String,Object>> result_count=executeQuery(session,sql_count,parm);
            if(result_count!=null&&result_count.size()>0){
                Map<String,Object> res=result_count.get(0);
                total= (Long) res.get(SQL_COUNT_PREFIC);
            }
        }else {
            logger.error("[class:HibernateUtils][function:executeCount][get sql error][sql={}][sql_count={}]",sql,sql_count);
        }
        return total==null?0l:total;
    }

    /**
     * 执行查询语句
     * @param session hibernate当前的session
     * @param sql   sql语句必须包含where字句，如果没有条件，则需要在传入之前拼接 where 1=1
     * @param parm   参数列表
     * @return
     */
    public static List<Map<String, Object>> executeQuery(Session session,String sql,Object[] parm) {
        logger.debug("[class:HibernateUtil][function:executeQuery][sql={}][param={}]",sql,parm);
        sql=disploseSql(sql); //sql预处理，包括全部转为小写
        if(sql==null||sql.length()<=0){
            return null;
        }
        Connection conn = session.connection();
        ResultSet res = null;
        PreparedStatement pstmt = null;
        List<Map<String, Object>> result=new ArrayList<Map<String, Object>>();
        try {
            pstmt = conn.prepareStatement(sql); //准备查询语句
            if(parm !=null){
                if (parm != null && parm.length > 0) {
                    for (int i = 0; i < parm.length; i++) {
                        if (parm[i] == null) {
                            break;
                        }
                        pstmt.setObject(i + 1, parm[i]);
                    }
                }
            }
            res = pstmt.executeQuery();  //执行查询
            if (res != null) {
                int columnCount = res.getMetaData().getColumnCount();
                while (res.next()) {
                    Map<String, Object> resultRow = new HashMap<String, Object>();
                    for (int i = 1; i <= columnCount; i++) {
                        resultRow.put(res.getMetaData().getColumnLabel(i), res.getObject(i));
                    }
                    result.add(resultRow);
                    resultRow = null;
                }
            }
        } catch (SQLException e) {
            logger.error("[class:HibernateUtil][function:executeQuery][executeQuery error][sql={}][param={}][exception={}]",sql,parm,e);
        }finally {
            closeRes(res);
            closePstmt(pstmt);
        }
        return result;
    }

    /**
     * 关闭statement
     * @param pstmt
     */
    public static void closePstmt(PreparedStatement pstmt) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (Exception ex) {
                logger.error("[class:HibernateUtil][function:closePstmt][closePstmt error][exception={}]",ex);
            }
        }
    }

    /**
     * 关闭结果集
     * @param res
     */
    public static void closeRes(ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (Exception ex) {
                logger.error("[class:HibernateUtil][function:closeRes][closeRes error][exception={}]",ex);
            }
        }
    }

    /**
     * 预处理Sql语句
     * @param sql
     * @return
     */
    private static String disploseSql(String sql) {
        StringBuilder sql_result=new StringBuilder(StringUtils.lowerCase(sql));
        if(sql_result.indexOf("where")<=0){ //不包含where字句认为不合法的sql
            return null;
        }
        return sql_result.toString();
    }

    /**
     * 根据查询sql语句获取count语句
     * @param sql
     * @return
     */
    private static String getCountSql(String sql) {
        String sqlCount="from "+StringUtils.substringAfter(sql,"from"); //滤掉select子句
        sqlCount=StringUtils.substringBefore(sqlCount,"order by");  //滤掉order字句

        if(sqlCount.length()>0){
            return "select "+SQL_COUNT_PREFIC+" "+sqlCount; //拼接count字句
        }
        return "";
    }

    /**
     * 将查询结果转为List
     * @param mapList
     * @param clazzName
     * @return
     */
    public static List convertToBeanList(List<Map<String,Object>> mapList,String clazzName){
        List result=new ArrayList();
        for (Map map:mapList){
            result.add(convertToBean(map,clazzName));
        }
        return result;
    }

    /**
     * 将查询结果转为javaBean
     * @param map
     * @param clazzName
     * @return
     */
    public static Object convertToBean(Map<String,Object> map,String clazzName){
        Object clazz= null;
        try {
            clazz = (Object) Class.forName(clazzName).newInstance();
            for(String key: map.keySet()){
                String javaName= convertToJavaFieldName(key);
                try {
                    ReflectionUtils.setFieldValue(clazz, javaName, map.get(key));
                }catch (IllegalArgumentException ex) {
                    logger.error("[class:HibernateUtil][function:convertToBean][injection filed error][exception={}]", ex);
                }
            }
        } catch (Exception e) {
            logger.error("[class:HibernateUtil][function:convertToBean][build class error][exception={}]", e);
        }
        return clazz;

    }

    /**
     * 将数据库取出的字段名转为java变量名
     * Ex:
     * 1.update_time---->updateTime
     * 2.title---->title
     * 3.source_category_id---->sourceCategoryId
     * 4.videoFileId----->videoFileId
     * @param db_name
     * @return
     */
    public static String convertToJavaFieldName(String db_name) {
        StringBuilder sb=new StringBuilder();
        String[] db_nameArray=StringUtils.split(db_name,"_");
        if(db_nameArray==null||db_nameArray.length<=0){
            return db_name;
        }else {
            for (int i=0;i<db_nameArray.length;i++){
                String str=db_nameArray[i];
                String prefic=str.substring(0,1);
                sb.append(i==0?prefic:prefic.toUpperCase()).append(StringUtils.substringAfter(str,prefic));
            }
        }
        return sb.toString();
    }
}
